package com.example.dbmeta.meta.jpatable;

import com.example.dbmeta.exception.DbMetaBootException;
import com.example.dbmeta.exception.DbMetaErrorCode;
import com.example.dbmeta.meta.common.xml.XmlConfigLoader;
import com.example.dbmeta.meta.common.xml.DataInstance;
import com.example.dbmeta.meta.common.xml.XmlConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
@Slf4j
public class MetaTableService {
    private int keepmaxdays=60;
    @Autowired
    private MetaJpaRepository metaRepository;
    @Autowired
    private XmlConfigLoader xmlConfigLoader;

    //连接配置的数据库，具有配置表的信息
    // 每天凌晨5点执行，cron 表达式：0 0 5 * * ?
    @Scheduled(cron = "0 0 5 * * ?")
    public void execute() throws Exception {
        XmlConfig xmlConfig = xmlConfigLoader.loadDataInstancesFromXml("config.xml");
        List<DataInstance> dataInstanceList= xmlConfig.getDataInstances();
        intelligentdelete(xmlConfig);

        //List<TableInfo> tableInfos = new ArrayList<>();
        int dbinstancecount=0;
        for (DataInstance di : dataInstanceList) {
            dbinstancecount++;;
            try {

                //1.加载驱动程序
                Class.forName(di.getDriverclassname());
                //2.获得数据库链接
                Connection conn = DriverManager.getConnection(di.getUrl(), di.getUsername(), di.getPassword());
                //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
                Statement st = conn.createStatement();
                String sql = "SELECT *\n" +
                        "FROM information_schema.TABLES\n" +
                        "WHERE TABLE_SCHEMA NOT IN (\n" +
                        "    'information_schema',\n" +
                        "    'mysql',\n" +
                        "    'performance_schema',\n" +
                        "    'sys'\n" +
                        ")";

                ResultSet rs = st.executeQuery(sql);
                //            //4.处理数据库的返回结果(使用ResultSet类)
                List<TableMetaInfo> eachdbtableinfos=handlemappingrs(rs, di);

                metaRepository.saveAll(eachdbtableinfos);
                //eachdbtableinfos计算有多个库，根据table_schema进行分组，然后计算每个库有多少张表

                int dbcount= eachdbtableinfos.stream().collect(Collectors.groupingBy(TableMetaInfo::getTableSchema)).values().size();
                //写log日志，当前正在保存的是哪一个实例，该实例有多少个库，这个库有多少张表

                log.info("共计{}实例,当前正在保存的是第{}个实例，实例的名称为{}，该实例有{}个库，这个实例下有{}张表",dataInstanceList.size(),di.getDbinstancename(),dbinstancecount,dbcount,eachdbtableinfos.size());
                //tableInfos.clear();
                //关闭资源
                rs.close();
                st.close();
                conn.close();
            } catch (Exception ex) {
                System.out.println(ex);
            }
        }
        //return tableInfos;
    }



    private List<TableMetaInfo> handlemappingrs(ResultSet rs, DataInstance di) throws SQLException {
        List<TableMetaInfo> tableMetaInfos = new ArrayList<>();
        while (rs.next()) {
            TableMetaInfo info = new TableMetaInfo();
            info.setTableCatalog(rs.getString("TABLE_CATALOG"));
            info.setTableSchema(rs.getString("TABLE_SCHEMA"));
            info.setTableName(rs.getString("TABLE_NAME"));
            info.setTableType(rs.getString("TABLE_TYPE"));
            info.setEngine(rs.getString("ENGINE"));
            info.setVersion(rs.getObject("VERSION", Integer.class));
            info.setRowFormat(rs.getString("ROW_FORMAT"));
            info.setTableRows(rs.getObject("TABLE_ROWS", Long.class));
            info.setAvgRowLength(rs.getObject("AVG_ROW_LENGTH", Long.class));
            info.setDataLength(rs.getObject("DATA_LENGTH", Long.class));
            info.setMaxDataLength(rs.getObject("MAX_DATA_LENGTH", Long.class));
            info.setIndexLength(rs.getObject("INDEX_LENGTH", Long.class));
            info.setDataFree(rs.getObject("DATA_FREE", Long.class));
            info.setAutoIncrement(rs.getObject("AUTO_INCREMENT", Long.class));
            info.setCreateTime(rs.getTimestamp("CREATE_TIME"));
            info.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
            info.setCheckTime(rs.getTimestamp("CHECK_TIME"));
            info.setTableCollation(rs.getString("TABLE_COLLATION"));
            info.setChecksum(rs.getString("CHECKSUM"));
            info.setCreateOptions(rs.getString("CREATE_OPTIONS"));
            info.setTableComment(rs.getString("TABLE_COMMENT"));

            info.setDbinstancename(di.getDbinstancename());
            info.setNote(di.getNote());
            tableMetaInfos.add(info);
        }
        return tableMetaInfos;
    }
    @Transactional
    public void intelligentdelete(XmlConfig xmlConfig) throws Exception{

        Integer  keepdays = xmlConfig.getKeepdays();
        //获取数据库记录中最新的一天时间
        if(keepdays>0 && keepdays<=keepmaxdays){
            //删除今天的数据，然后在重新获取插入今天的数据
            deleteTodayData();

        }else {
            throw new DbMetaBootException(DbMetaErrorCode.CONFIG_PARSE_ERROR,"keepdays参数不合法,请检查，应该在0-60天之间");
        }

    }


    public void deleteTodayData() {
        // 获取当前时区
        ZoneId zone = ZoneId.systemDefault();
        // 今天的开始时间（本地时间的00:00:00）
        LocalDateTime startOfDay = LocalDate.now(zone).atStartOfDay();
        Instant start = startOfDay.atZone(zone).toInstant();

        // 明天的开始时间（本地时间）
        LocalDateTime startOfTomorrow = startOfDay.plusDays(1);
        Instant end = startOfTomorrow.atZone(zone).toInstant();

        metaRepository.deleteByCreateTimeBetween(start, end);
    }
    public void deleteDataOlderThan60Days() {
        // 获取系统默认时区
        ZoneId zone = ZoneId.systemDefault();

        // 计算60天前的开始时间（本地时间的00:00:00）
        LocalDateTime sixtyDaysAgo = LocalDate.now(zone).minusDays(keepmaxdays).atStartOfDay();

        // 将60天前的开始时间转换为Instant
        Instant cutoffTime = sixtyDaysAgo.atZone(zone).toInstant();

        // 删除所有创建时间早于60天前的数据
        metaRepository.deleteByCreateTimeBefore(cutoffTime);

        // 记录日志
        log.info("已删除所有创建于 {} 之前的数据", sixtyDaysAgo);
    }
}